﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Common.ModelConvert;

namespace Common.DataBaseHelper
{

    /// <summary>
    /// SqlServer数据库数据交互帮助类
    /// </summary>
    public class SqlServerHelper
    {

        //在.Net Framework 下从 Web.Config文件或App.Config 文件中获取指定配置节点下的数据库连接字符串
        //private static readonly string ConStr = ConfigurationManager.ConnectionStrings["MySqlConn"].ConnectionString;


        public static int ExecuteNonQuery(SqlConnection con, string sql, CommandType cmdType, object pms)
        {
            var sqlPms = ObjectToSqlParamArray(pms);
            var executeResult = ExecuteNonQuery(con, sql, cmdType, sqlPms);
            return executeResult;
        }

        /// <summary>
        /// 执行 Insert, Delete, Update语句的操作
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="sql">需要执行的sql语句</param>
        /// <param name="cmdType">sql语句类型</param>
        /// <param name="pms">sql语句的参数</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteNonQuery(SqlConnection con, string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            using var cmd = new SqlCommand(sql, con)
            {
                CommandType = cmdType
            };

            if (pms != null && pms.Length != 0)
            {
                cmd.Parameters.AddRange(pms);
            }

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            return cmd.ExecuteNonQuery();
        }

        public static object ExecuteScalar(SqlConnection con, string sql, CommandType cmdType, object pms)
        {
            var sqlPms = ObjectToSqlParamArray(pms);
            var executeResult = ExecuteScalar(con, sql, cmdType, sqlPms);
            return executeResult;
        }

        /// <summary>
        /// 返回查询的结果中第一行第一列的第一个值
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="sql">需要执行的sql语句</param>
        /// <param name="cmdType">sql语句类型</param>
        /// <param name="pms">sql语句的参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlConnection con, string sql, CommandType cmdType, params SqlParameter[] pms)
        {

            using var cmd = new SqlCommand(sql, con)
            {
                CommandType = cmdType
            };

            if (pms != null && pms.Length != 0)
            {
                cmd.Parameters.AddRange(pms);
            }

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }


            return cmd.ExecuteScalar();
        }



        /// <summary>
        /// 返回SqlDataReader对象
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="sql">需要执行的sql语句</param>
        /// <param name="cmdType">sql语句类型</param>
        /// <param name="pms">sql语句的参数</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(SqlConnection con, string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            using var cmd = new SqlCommand(sql, con)
            {
                CommandType = cmdType
            };

            if (pms != null && pms.Length != 0)
            {
                cmd.Parameters.AddRange(pms);
            }

            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }

                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                con.Close();
                con.Dispose();
                throw;
            }
        }

        /// <summary>
        /// 返回DateTable对象
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="sql">需要执行的Sql语句对象</param>
        /// <param name="cmdType">sql语句类型</param>
        /// <param name="pms">sql参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(SqlConnection con, string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            var resultDateTable = new DataTable();

            using var adapter = new SqlDataAdapter(sql, con)
            {
                SelectCommand = { CommandType = cmdType }
            };

            if (pms != null && pms.Length != 0)
            {
                adapter.SelectCommand.Parameters.AddRange(pms);
            }
            adapter.Fill(resultDateTable);
            return resultDateTable;
        }

        /// <summary>
        /// 返回DateSet对象
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="sql">需要执行的sql语句对象</param>
        /// <param name="cmdType">sql语句类型</param>
        /// <param name="pms">sql参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDateDataSet(SqlConnection con, string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            var resultDataSet = new DataSet();

            using var adapter = new SqlDataAdapter(sql, con)
            {
                SelectCommand = { CommandType = cmdType }
            };
            if (pms != null && pms.Length != 0)
            {
                adapter.SelectCommand.Parameters.AddRange(pms);
            }
            adapter.Fill(resultDataSet);
            return resultDataSet;
        }


        //-----------包装转换-----------

        /// <summary>
        /// 查询 返回指定类型的数据集合
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="sql">需要执行的Sql语句对象</param>
        /// <param name="cmdType">sql语句类型</param>
        /// <param name="pms">sql参数 要求是实体类</param>
        /// <returns></returns>
        public static List<T> ExecuteToEntities<T>(SqlConnection con, string sql, CommandType cmdType, object pms) where T : new()
        {
            var sqlPms = ObjectToSqlParamArray(pms);

            var resultDateTable = ExecuteDataTable(con, sql, cmdType, sqlPms);
            var resultList = ConvertUtils.DataTableToEntities<T>(resultDateTable);

            return resultList;
        }

        /// <summary>
        /// 查询 返回指定类型的数据集合数据中第一条
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="sql">需要执行的Sql语句对象</param>
        /// <param name="cmdType">sql语句类型</param>
        /// <param name="pms">sql参数 要求是实体类</param>
        /// <returns></returns>
        public static T ExecuteToEntityFirst<T>(SqlConnection con, string sql, CommandType cmdType, object pms) where T : new()
        {
            var resultList = ExecuteToEntities<T>(con, sql, cmdType, pms);
            return resultList[0];
        }

        /// <summary>
        /// 查询 返回指定类型的数据集合
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="sql">需要执行的Sql语句对象</param>
        /// <param name="cmdType">sql语句类型</param>
        /// <param name="pms">sql参数 要求是实体类</param>
        /// <returns></returns>
        public static List<T> ExecuteToEntities<T>(SqlConnection con, string sql, CommandType cmdType, Dictionary<string, object> pms) where T : new()
        {
            var sqlPms = DictionaryToSqlParamArray(pms);
            var resultDateTable = ExecuteDataTable(con, sql, cmdType, sqlPms);
            var resultList = ConvertUtils.DataTableToEntities<T>(resultDateTable);

            return resultList;
        }

        /// <summary>
        /// 查询 返回指定类型的数据集合
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="sql">需要执行的Sql语句对象</param>
        /// <param name="cmdType">sql语句类型</param>
        /// <param name="outputValue">存储过程输出参数值</param>
        /// <param name="pms">sql参数</param>
        /// <returns></returns>
        public static List<T> ExecuteToEntities<T>(SqlConnection con, string sql, CommandType cmdType, out Dictionary<string, object> outputValue, object pms) where T : new()
        {
            var sqlPms = ObjectToSqlParamArray(pms);
            var executeResult = ExecuteToEntities<T>(con, sql, cmdType, out outputValue, sqlPms);
            return executeResult;
        }

        /// <summary>
        /// 查询 返回指定类型的数据集合
        /// </summary>
        /// <param name="con">数据库连接对象</param>
        /// <param name="sql">需要执行的Sql语句对象</param>
        /// <param name="cmdType">sql语句类型</param>
        /// <param name="outputValue">存储过程输出参数值</param>
        /// <param name="pms">sql参数</param>
        /// <returns></returns>
        public static List<T> ExecuteToEntities<T>(SqlConnection con, string sql, CommandType cmdType, out Dictionary<string, object> outputValue, params SqlParameter[] pms) where T : new()
        {
            outputValue = new Dictionary<string, object>();
            var resultDateTable = new DataTable();

            using var adapter = new SqlDataAdapter(sql, con)
            {
                SelectCommand = { CommandType = cmdType }
            };

            var paramExist = pms != null && pms.Length != 0;

            if (paramExist)
            {
                adapter.SelectCommand.Parameters.AddRange(pms);
            }
            adapter.Fill(resultDateTable);
            var resultList = ConvertUtils.DataTableToEntities<T>(resultDateTable);

            if (paramExist == false) return resultList;

            foreach (var p in pms)
            {
                if (p.Direction != ParameterDirection.Output) continue;
                var outParamName = p.ParameterName.Replace("@", string.Empty);
                outputValue[outParamName] = p.Value;
            }

            return resultList;
        }

        #region 内部成员
        /// <summary>
        /// 将实体参数转换成sql参数
        /// </summary>
        /// <param name="pms"></param>
        /// <returns></returns>
        private static SqlParameter[] ObjectToSqlParamArray(object pms)
        {
            var sqlPms = new List<SqlParameter>();

            if (pms == null) return sqlPms.ToArray();
            
            foreach (var p in pms.GetType().GetProperties())
            {
                sqlPms.Add(new SqlParameter("@" + p.Name, p.GetValue(pms)));
            }

            return sqlPms.ToArray();
        }

        /// <summary>
        /// 将键值对参数转换成sql参数
        /// </summary>
        /// <param name="pms"></param>
        /// <returns></returns>
        private static SqlParameter[] DictionaryToSqlParamArray(Dictionary<string, object> pms)
        {
            var sqlPms = new List<SqlParameter>();

            if (pms == null || pms.Count == 0) return sqlPms.ToArray();

            foreach (var (key, value) in pms)
            {
                sqlPms.Add(new SqlParameter("@" + key, value));
            }

            return sqlPms.ToArray();
        }
        #endregion
    }
}